package dataClean;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import static org.apache.poi.ss.usermodel.Cell.*;

/**
 * @author Smartloe
 * @create 2021-04-29 20:02
 */
public class ExcelClean1 {
    public static void main(String[] args) {
        String fileName1 = "src\\main\\java\\dataClean\\data\\excelclean1.xlsx";
        poiExcel(fileName1);
    }

    //清洗缺失值（替换：错误年龄，空值，无效值）
    public static void poiExcel(String fileName) {
        boolean is2007 = true;    //判断是否是excel2007格式
        if (fileName.endsWith("xlsx")) {
            is2007 = false;
        }
        try {
            InputStream input = new FileInputStream(fileName);  //建立输入流
            Workbook wb;
            //根据文件格式(2003或者2007)来初始化
            if (is2007) {
                wb = new HSSFWorkbook(input);
            } else {
                wb = new XSSFWorkbook(input);
            }
            //getSheetAt(index:0)表示从wb中获得第一个表格对象。
            Sheet sheet = wb.getSheetAt(0);
            List<List<Serializable>> lst_total = new ArrayList<List<java.io.Serializable>>();
            List<java.io.Serializable> lst_age = new ArrayList<Serializable>();
            int max_age = 0;
            int min_age = 50;
            int sum_age =0;
            int count = 0;
            double age_count_invalid = 0;
            double age_count_valid = 0;
            int lastRowNum = sheet.getLastRowNum();
            for (int i = 1; i <= lastRowNum; i++) {
                //这里很重要,因为在这里定义ArrayList,保证了每次行循环的时候,都是一个空的lst去接当前行的数据.
                List<java.io.Serializable> lst = new ArrayList<java.io.Serializable>();
                //具体到某一行
                Row row = sheet.getRow(i);
                /*
                这里可以举一反三，通过操作每行row的单元cell数组下标，进行判断和处理所有数据。并将单列数据单独存储，最后与其它数据整合。
                 */
                //单独判断整合年龄字段,将年龄在20-100之外的数据用60填充。
                double age1 = row.getCell(7).getNumericCellValue();
                int age_int1 = (int) age1;
                if (age_int1 > 20 && age_int1 < 100) {
                    age_count_valid++;
                    lst_age.add(age_int1);
                } else {
                    age_count_invalid++;
                    age_int1 = 60;
                    lst_age.add(age_int1);
                }

                //单元格的数量
                int lastCellNum = row.getLastCellNum();
                for (int j = 0; j < lastCellNum - 1; j++) {
                    Cell cell = row.getCell(j);
                    //根据cell中的类型来输出数据
                    switch (cell.getCellType()) {
                        case CELL_TYPE_NUMERIC:
                            //这里用于区分年龄和发货日期。年龄在20-100之间的数字都是合法年龄，因为日期数据比较大。
                            //因此，这里可以发现非法年龄。
                            if (cell.getNumericCellValue() > 20 && cell.getNumericCellValue() < 100) {
                                double age = cell.getNumericCellValue();
                                int age_int = (int) age;
                                lst.add(age_int);
                            } else {
                                //清洗日期数据格式。
                                short format = cell.getCellStyle().getDataFormat();
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                                if (format != 0) {
//                            format == 176时是当指定单元格格式为日期是都是176
                                    if (format == 14 || format == 31 || format == 57 || format == 58 || format == 176) {
                                        //日期
                                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                                    } else if (format == 20 || format == 32) {
                                        //时间
                                        sdf = new SimpleDateFormat("HH:mm");
                                    }
                                    double value = cell.getNumericCellValue();
                                    Date date = DateUtil.getJavaDate(value);
                                    String result = sdf.format(date);
                                    lst.add(result);
                                }
                            }
                            break;
                        case CELL_TYPE_STRING:
//                            System.out.print(cell.getStringCellValue());
//                            System.out.print("|");
                            //对所有内容为"unknown"和"未知"的单元进行替换。
                            if (cell.getStringCellValue().equals("unknown")||cell.getStringCellValue().equals("未知")) {
                                String s = cell.getStringCellValue();
                                s = "invalid";
                                lst.add(s);
                            } else {
                                lst.add(cell.getStringCellValue());
                            }
                            break;
                        case CELL_TYPE_BOOLEAN:
//                            System.out.print(cell.getBooleanCellValue());
//                            System.out.print("|");
                            lst.add(cell.getBooleanCellValue());
                            break;
                        case CELL_TYPE_FORMULA:
//                            System.out.print(cell.getCellFormula());
//                            System.out.print("|");
                            lst.add(cell.getCellFormula());
                            break;
                        case CELL_TYPE_BLANK:
//                            System.out.print("blank");
//                            System.out.print("|");
                            //用于判断和填充空值。
                            lst.add("blank");
                            break;
                        case CELL_TYPE_ERROR:
//                            System.out.print("error");
//                            System.out.print("|");
                            lst.add("error");
                            break;
                        default:
//                            System.out.print("unsuported sell type");
//                            System.out.print("|");
                            lst.add("unsuported sell type");
                            break;
                    }
                }
                lst_total.add(lst);
            }

            //求年龄最大值
            Iterator<Serializable> iterator1 = lst_age.iterator();
            while (iterator1.hasNext()){
                int age = (int) iterator1.next();
                if(age > max_age){
                    max_age = age;
                }
            }
            //求年龄最小值
            Iterator<java.io.Serializable> iterator2 = lst_age.iterator();
            while (iterator2.hasNext()){
                int age = (int) iterator2.next();
                if(age < min_age){
                    min_age = age;
                }
            }

            //求年龄平均值
            Iterator<Serializable> iterator3 = lst_age.iterator();
            while (iterator3.hasNext()){
                int age = (int) iterator3.next();
                sum_age+= age;
                count++;
            }
            System.out.println("最大有效年龄："+max_age);
            System.out.println("最小有效年龄："+min_age);
            System.out.println("平均年龄："+ sum_age/count);
            System.out.println("年龄数据有效率:"+age_count_valid/(age_count_invalid+age_count_valid));
            //实现两个List<java.io.Serializable>的合并。
            int i;
            for (i = 0; i < lst_total.size(); i++) {
                lst_total.get(i).add(lst_age.get(i));
                System.out.println(lst_total.get(i));
            }
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}
